8 個Excel Tricks (小秘訣) 💡, 幫你提升工作效率|質量 👍

[@brightlightdun]

快快快靚靚靚正正正😂

[@KKLeung]

@@brightlightdun 多謝收看

[@amyleung7903]

好清楚 謝謝你

[@KKLeung]

@@amyleung7903 Hi Amy,
多謝你嘅好評，協助轉發俾有需要嘅朋友，就係對本平台嘅義工最大嘅支持🙏😀

[@kinfaichap2838]

謝謝

[@KKLeung]

@@kinfaichap2838 多謝收看

[@azalea732]

Thank you for the session! Question about slicer filter: can I use multi columns to perform the slicer filter function? thanks.

[@KKLeung]

@@azalea732 Thanks for watching.
Yes, you can use multiple slicers to analyse the data set.

[@stevenlylaw7667]

Hello, KK, are these functions only available in 360?

[@KKLeung]

@@stevenlylaw7667 only trick #1

[@Teen1-vi1rj]

Thank you very much

[@bonniechong2868]

我想請問如果我的excel 版本(不是365）出不到wraprows 函數，可否有其他函數做到wraprows 功能？ 非常謝謝！

[@KKLeung]

@@bonniechong2868 Hi Bonnie
多謝收看
依個係Excel 365 嘅新function. 沒有諗到其他功能可代替。或者你可以用Excel web 版，係免費的。

[@bonniechong2868]

謝謝你

[@wiman852]

老師，您好！有否方法防止已設定cell的輸入條件，被paste by “123”模式已破壞? 谢谢！

[@KKLeung]

Hi Wi Man,
你所述情況, 應該是要用vba code先可以做到這個要求. 請按下嚟步驟
1 選擇需要控制worksheet名嘅位置, mouse按右鍵
2 選擇View Code
3 將下面 VBA program copy 到VBA editor
4 更改需要控制嘅範圍, 現時例子係A1:B10
5 在左上角按Excel icon, 返回worksheet版面.
6 嘗試設定功能
7 最後File 必須Save as Excel Macro-enabled Workbook
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:B10")) Is Nothing Then
For Each cell In Target
If Not IsEmpty(cell) Then
If IsNumeric(cell.Value) Then
MsgBox "Only Text input is allowed"
cell.ClearContents
End If
End If
Next cell
End If
Application.EnableEvents = True
End Sub

[@wiman852]

明白了！感谢老師，解答！🌷🌷🌷

[@poonsteve8019]

How to set protect an input area once user input data into it. Or some rules that input data is satisfied then input area protection is enforced.

[@KKLeung]

Thanks for watching. Very likely, you need to have VBA CODE to meet the requirements.
KK

[@poonsteve8019]

@@KKLeung I found my case doesn’t have VBA. I input a specified area and save it once. Then the input area can’t be overriding that area. Because it is not my file and I am still checking it. Anyway thanks for your reply.

[@KKLeung]

@@poonsteve8019 what is the extension of the file? Is it a xlsm?

[@poonsteve8019]

@@KKLeung yes

[@KKLeung]

@@poonsteve8019 the file has VBA program to lock the cells after you input and saved.

[@SeewaiCheung]

請問aggregate 同subtotal 有乜分別？

[@KKLeung]

SUBTOTAL 函數
用於計算數據的總和、平均值、計數等，可以選擇不同的計算方式。
通常用於篩選的數據集，能夠忽略隱藏的行（例如，通過過濾功能隱藏的行）。
類型代碼：有不同的類型代碼（如 1 代表平均值，9 代表總和等）。
AGGREGATE 函數
提供更多計算選項，除了支持 `SUBTOTAL` 的所有功能外，還可以進行更高級的計算（如標準差、方差等）。
可以選擇是否忽略錯誤值和隱藏行，提供更多靈活性。